package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.*;

public class DbContact implements IFDBContact {
	private Connection con;
		public DbContact()
		{
			con = DbConnection.getInstance().getDBcon();
		}
		
		@Override
		public ArrayList<Private> getAllPrivateCustomers() {
			return miscWhere("");
		}

		@Override
		public Private getCustomerByCPR(String CPR) {
			String wClause = "cpr = '" + CPR + "'";
			System.out.println("Search Customer " + wClause);
			return singleWhere(wClause);
		}

		@Override
		public Private getCustomerByName(String name) {
			String wClause = "name = '" + name + "'";
			System.out.println("Search Customer " + wClause);
			return singleWhere(wClause);
		}
		
		@Override
		public Private getCustomerById(int id) {
			String wClause = "id = '" + id + "'";
			System.out.println("Search Customer " + wClause);
			return singleWhere(wClause);
		}

		@Override
		public int insertCustomer(Contact customer) throws Exception  {
			int nextID = GetMax.getMaxId("SELECT max(id) from Contact");
			nextID++;
			int rc = -1;
			String insertQuery="INSERT INTO Contact(id, address, city, zipCode, phoneNo, eMail, status, type";
			String valueQuery = "VALUES('"+
					nextID  + "','"  +
					customer.getAddress() + "','"  +
					customer.getCity() + "','"  +
					customer.getZipCode() + "','"  +
					customer.getPhoneNO() + "','"  +
					customer.getEMail() + "','"  +
					customer.getStatus() + "','";

			String queryContact = "";
			
			String queryCustomer = "";
			
			if(customer instanceof Private)
			{
				queryContact = insertQuery + ") " + valueQuery + "private')";
				
				Private privateCustomer = (Private) customer;
				
				insertQuery="INSERT INTO Private(id, cpr, name, surname)";
				valueQuery="VALUES('"+nextID + "','"  +
						privateCustomer.getCPR() + "','"  +
						privateCustomer.getName() + "','" +
						privateCustomer.getSurname()  + "'";
				
				queryCustomer = insertQuery + ") " + valueQuery + ")";
			}
			else
			{
				queryContact = insertQuery + ") " + valueQuery + "business')";
				
				Business businessCutomer = (Business) customer;
				
				insertQuery="INSERT INTO Business(id, cpr, name, surname)";
				valueQuery="VALUES('"+nextID + "','"  +
						businessCutomer.getCVR() + "','"  +
						businessCutomer.getName() + "'";
				
				queryCustomer = insertQuery + ") " + valueQuery + ")";
			}
			
			System.out.println("insert Contact: " + queryContact);
			System.out.println("insert Private: " + queryCustomer);
			try{ // insert new customer
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(queryContact);
				rc = stmt.executeUpdate(queryCustomer);
				stmt.close();
			}//end try
			catch(SQLException ex){
				System.out.println("Customer not created");
				throw new Exception("Customer is not inserted correctly");
			}
			return(rc);
		}

		@Override
		public int updateCustomer(Contact customer) {
			int rc=-1;

			String queryContact="UPDATE Contact SET "+
					"address = '" + customer.getAddress() + "', "+
					"city = '" + customer.getCity() + "', " +
					"zipCode = '" + customer.getZipCode() + "', " +
					"phoneNo = '" + customer.getPhoneNO() + "', " +
					"eMail = '" + customer.getEMail() + "', " +
					"status = '" + customer.getStatus() + "'" +
					" WHERE id = '"+ customer.getId() + "'";
			
			String queryPrivate="UDATE Private SET "+
					"cpr = '"+ customer.getCPR()+ "', " +
					"name = '"+ customer.getName() + "', " +
					"surname = '"+ customer.getSurname() + "'" +
					" WHERE id = '"+ customer.getId() + "'";
			
			System.out.println("Update Contact: " + queryContact);
			System.out.println("Update Private: " + queryPrivate);
			try{ // update Contact + Private
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(queryContact);
				rc = stmt.executeUpdate(queryPrivate);

				stmt.close();
			}//end try
			catch(Exception ex){
				System.out.println("Update exception in Private db: "+ex);
			}
			return(rc);
		}

		@Override
		public int deleteCustomer(Contact customer) {
			int rc=-1;

			String query="DELETE FROM Private WHERE id = '" +
					customer.getId() + "'";
			System.out.println(query);
			try{ // delete from private
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(query);
				stmt.close();  
			}//end try	
			catch(Exception ex){
				System.out.println("Delete exception in Private db: "+ex);
			}
			return(rc);
		}

		//private methods

		//michWere is used whenever we want to select more than one private customer
		private ArrayList<Private> miscWhere(String wClause)
		{
			ResultSet results;
			ArrayList<Private> list = new ArrayList<Private>();	
			String query =  buildQuery(wClause);

			try{ // read the PrivateCustomer from the database
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);

				while( results.next() )
				{
					Private custObj = new Private();
					custObj = buildCustomer(results);				
					list.add(custObj);		
				}//end while
				stmt.close();     

			}//end try	
			catch(Exception e){
				System.out.println("Query exception - select: "+e);
				e.printStackTrace();
			}

			return list;
		}

		//Singlewhere to select only one Private Customer 	
		private Private singleWhere(String wClause)
		{
			ResultSet results;
			Private custObj = new Private();
			
		
				String query =  buildQuery(wClause);
				System.out.println(query);
				try{ // read the Private Customer from the database
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					results = stmt.executeQuery(query);

					if( results.next() ){
						custObj = buildCustomer(results);
						stmt.close();
					}		
					else{ //no customer was found
						custObj = null;
					}
				}
				catch(Exception e){
					System.out.println("Query exception: "+e);
				}

			return custObj;
		}

		//method to build the query for Customer
		private String buildQuery(String wClause)
		{
			String query="SELECT c.id, address, city, zipCode, phoneNo, eMail, status, "
					+ "cpr, name, surname FROM Contact c, Private p "
					+ "WHERE p.id = c.id";
			
			if (wClause.length()>0)
				query=query+" AND " + wClause;

			return query;
		}

		//method to build a Customer object
		private Private buildCustomer(ResultSet results)
		{   
			Private customerObj = new Private();
			try{ // the columns from the table customer  are used
				customerObj.setId(results.getInt("id"));
				customerObj.setAddress(results.getString("address"));
				customerObj.setCity(results.getString("city"));
				customerObj.setZipCode(results.getString("zipCode"));
				customerObj.setPhoneNO(results.getString("phoneNo"));
				customerObj.setEMail(results.getString("eMail"));
				customerObj.setStatus(results.getString("status"));
				customerObj.setCPR(results.getString("cpr"));
				customerObj.setName(results.getString("name"));
				customerObj.setSurname(results.getString("surname"));
			}
			catch(Exception e)
			{
				System.out.println("error in building the customer object");
			}
			return customerObj;
		}
}
